<?php


namespace app\common\model;

use app\api\model\UserException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;


class Excel extends Base
{

    /**
     * 导出控制
     * @param string $title
     * @param array $head
     * @param array $data
     * @param int $method
     */
    static public function export($title='export',$head=[],$data=[],$method=2){
        if($method==1){
            self::exportExcel($title,$head,$data);
        }else{
            self::exportCsv($title,$head,$data);
        }
    }

    /**
     * 导出excel
     * @param string $title 表名
     * @param array $head 标题 一维数组 数字键名
     * @param array $data 数据 二维数组 数字键名
     */
    static public function exportExcel($title='export',$head=[],$data=[]){

        // Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 使用 setCellValueByColumnAndRow
        //设置单元格内容
        //设置表头
        foreach ($head as $key => $value) {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        // sheet1 基础安全需求
        $row = 2; // 从第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }

        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$title.date('_YmdHis').'.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
//        exit;
    }

    /**
     * 导出csv
     * @param $filename
     * @param array $tileArray
     * @param array $dataArray
     */
    static public function exportCsv($filename, $tileArray=[], $dataArray=[]){
        set_time_limit(0);

        $fileName = $filename . date('_YmdHis'). '.csv';// 文件名称可根据自己情况设定
        $fp = fopen('./'.$fileName , 'w'); //生成临时文件

        $bom =  chr(0xEF).chr(0xBB).chr(0xBF);

        if(isset($tileArray[0])){
            $tileArray[0] = $bom.$tileArray[0];
        }

        fputcsv($fp, $tileArray);

        foreach ($dataArray as $line)
        {
            fputcsv($fp,$line);
        }

        fclose($fp);
        // 输出Excel文件头，可把user.csv换成你要的文件名
        header('Content-Type: text/csv;charset=gb2312');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Cache-Control: max-age=0');
        header('Content-Length: ' . filesize($fileName)); //
        @readfile('./'.$fileName);//输出文件;
        @unlink($fileName);
//        exit();
    }

    /**
     * 读取excel
     * @param string $filePath 文件路径地址
     * @param int $sheet 文件的sheet序号，从0开始
     * @param int $columnCnt 每个sheet读多少个字段
     * @param array $options
     * @return array 二维数组，每一个元素为一个数组，表示一列的数据。
     *               键为A,B,C的列号，值为该行所对应列的单元格内的值，第一个元素为表头
     * @throws UserException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    static public function readExcel(string $filePath = '', int $sheet = 0, int $columnCnt = 0, &$options = [])
    {
        /* 转码 */
        $filePath = iconv("utf-8", "gb2312", $filePath);

        if (empty($filePath) or !file_exists($filePath)) {
            throw new UserException('文件不存在!');
        }

        /** @var Xlsx $objRead */
        $objRead = IOFactory::createReader('Xlsx');

        if (!$objRead->canRead($filePath)) {
            /** @var Xls $objRead */
            $objRead = IOFactory::createReader('Xls');
        }

        if (!$objRead->canRead($filePath)) {
            $objRead = IOFactory::createReader('Csv')
                ->setDelimiter(',')
                ->setInputEncoding('GBK') //不设置将导致中文列内容返回boolean(false)或乱码
                ->setEnclosure('"')
                ->setSheetIndex(0);
        }

        if (!$objRead->canRead($filePath)) {
            throw new UserException('只支持导入csv、xls、xlsx文件！');
        }

        /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
        empty($options) && $objRead->setReadDataOnly(true);
        /* 建立excel对象 */
        $obj = $objRead->load($filePath);
        /* 获取指定的sheet表 */
        $currSheet = $obj->getSheet($sheet);

        if (isset($options['mergeCells'])) {
            /* 读取合并行列 */
            $options['mergeCells'] = $currSheet->getMergeCells();
        }

        if (0 == $columnCnt) {
            /* 取得最大的列号 */
            $columnH = $currSheet->getHighestColumn();
            /* 兼容原逻辑，循环时使用的是小于等于 */
            $columnCnt = Coordinate::columnIndexFromString($columnH);
        }

        /* 获取总行数 */
        $rowCnt = $currSheet->getHighestRow();
        $data   = [];

        /* 读取内容 */
        for ($_row = 1; $_row <= $rowCnt; $_row++) {
            $isNull = true;

            for ($_column = 1; $_column <= $columnCnt; $_column++) {
                $cellName = Coordinate::stringFromColumnIndex($_column);
                $cellId   = $cellName . $_row;
                $cell     = $currSheet->getCell($cellId);

                if (isset($options['format'])) {
                    /* 获取格式 */
                    $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                    /* 记录格式 */
                    $options['format'][$_row][$cellName] = $format;
                }

                if (isset($options['formula'])) {
                    /* 获取公式，公式均为=号开头数据 */
                    $formula = $currSheet->getCell($cellId)->getValue();

                    if (0 === strpos($formula, '=')) {
                        $options['formula'][$cellName . $_row] = $formula;
                    }
                }

                if (isset($format) && 'm/d/yyyy' == $format) {
                    /* 日期格式翻转处理 */
                    $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                }

                $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                if (!empty($data[$_row][$cellName])) {
                    $isNull = false;
                }
            }

            /* 判断是否整行数据为空，是的话删除该行数据 */
            if ($isNull) {
                unset($data[$_row]);
            }
        }

        return $data;
    }
}